package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model.Emp;
import model.RewAndPun;
import model.RewAndPunType;
import util.DBUtil;
import util.Page;
import dao.RewAndPunDAO;

public class RewAndPunDAOImpl implements RewAndPunDAO {
	DBUtil dbu = new DBUtil();
	Connection conn = null;
	PreparedStatement stat = null;
	ResultSet rs = null;
	boolean result=false;
	SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
	
	public boolean add(RewAndPun rewAndPun) {
		try {		
			conn=dbu.getConnection();
			String sql="insert into rewAndPun (ID,TITLE,EMPID,REWANDPUNTYPE,TIME,CONTENT,MONEY)values(rewAndPunSEQ.nextval,?,?,?,?,?,?)";			
			stat=conn.prepareStatement(sql);
			stat.setString(1, rewAndPun.getTitle());
			
			stat.setInt(2, rewAndPun.getEmp().getId());
			stat.setInt(3, rewAndPun.getRewAndPunType().getId());
			
			stat.setDate(4, new java.sql.Date(rewAndPun.getTime().getTime()));
			stat.setString(5, rewAndPun.getContent());
			stat.setDouble(6, rewAndPun.getMoney());

			if(stat.executeUpdate()>0){
				result=true;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbu.closeConnection(conn, stat, rs);
		}
		
		return result;
	}

	public boolean deleteByEmpId(int empid) {
		try {
			conn=dbu.getConnection();
			stat=conn.prepareStatement("delete from rewandpun where empid=?");
			stat.setInt(1, empid);
			
			if (stat.executeUpdate()>0) {
				result=true;
			}
		} catch (Exception e) {
			
		}
		return result;
	}

	public boolean deleteById(int id) {
		try {
			conn=dbu.getConnection();
			stat=conn.prepareStatement("delete from rewandpun where id=?");
			stat.setInt(1, id);
			
			if (stat.executeUpdate()>0) {
				result=true;
			}
		} catch (Exception e) {
			
		}
		return result;
	}

	public Page<RewAndPun> getAll(int pageNo, int pageSize) {
		Page<RewAndPun> page = new Page<RewAndPun>(pageNo, pageSize);
		int totalCount = 0;
		List<RewAndPun> list = new ArrayList<RewAndPun>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from RewAndPun";
			stat=conn.prepareStatement(sql);
			rs=stat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from RewAndPun) s where rownum <=?) where rn >=?";
			stat = conn.prepareStatement(sql);
			stat.setInt(1, page.getPageNo()*pageSize);
			stat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = stat.executeQuery();
			while (rs.next()) {
				RewAndPun rewAndPun =new RewAndPun();
				rewAndPun.setId(rs.getInt("ID"));
				rewAndPun.setTitle(rs.getString("TITLE"));
				rewAndPun.setEmp(new EmpDAOImpl().getEmpById(rs.getInt("EMPID")));
				rewAndPun.setRewAndPunType(new RewAndPunTypeDAOImpl().getById(rs.getInt("REWANDPUNTYPE")));
				rewAndPun.setTime(rs.getDate("TIME"));
				rewAndPun.setContent(rs.getString("CONTENT"));				
				rewAndPun.setMoney(rs.getDouble("MONEY"));

				list.add(rewAndPun);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return page;
	}

	public Page<RewAndPun> getByContent(int pageNo, int pageSize, String content) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<RewAndPun> getByEmpId(int pageNo, int pageSize, int empid) {
		Page<RewAndPun> page = new Page<RewAndPun>(pageNo, pageSize);
		int totalCount = 0;
		List<RewAndPun> list = new ArrayList<RewAndPun>();
		Emp emp = new Emp();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from RewAndPun";
			stat=conn.prepareStatement(sql);
			rs=stat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from RewAndPun) s where rownum <=?) where rn >=?";
			stat = conn.prepareStatement(sql);
			stat.setInt(1, emp.getId());
			stat.setInt(2, page.getPageNo()*pageSize);
			stat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = stat.executeQuery();
			while (rs.next()) {
				RewAndPun rewAndPun =new RewAndPun();
				rewAndPun.setId(rs.getInt("ID"));
				rewAndPun.setTitle(rs.getString("TITLE"));
				rewAndPun.setEmp(new EmpDAOImpl().getEmpById(rs.getInt("EMPID")));
				rewAndPun.setRewAndPunType(new RewAndPunTypeDAOImpl().getById(rs.getInt("REWANDPUNTYPE")));
				rewAndPun.setTime(rs.getDate("TIME"));
				rewAndPun.setContent(rs.getString("CONTENT"));				
				rewAndPun.setMoney(rs.getDouble("MONEY"));

				list.add(rewAndPun);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return page;

	}

	public Page<RewAndPun> getByRewAndPunType(int pageNo, int pageSize,
			RewAndPunType rewAndPunType) {
		Page<RewAndPun> page = new Page<RewAndPun>(pageNo, pageSize);
		int totalCount = 0;
		List<RewAndPun> list = new ArrayList<RewAndPun>();
		RewAndPunType rapt = new RewAndPunType();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from RewAndPun";
			stat=conn.prepareStatement(sql);
			rs=stat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from RewAndPun) s where rownum <=?) where rn >=?";
			stat = conn.prepareStatement(sql);
			stat.setInt(1, rapt.getId());
			stat.setInt(2, page.getPageNo()*pageSize);
			stat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = stat.executeQuery();
			while (rs.next()) {
				RewAndPun rewAndPun =new RewAndPun();
				rewAndPun.setId(rs.getInt("ID"));
				rewAndPun.setTitle(rs.getString("TITLE"));
				rewAndPun.setEmp(new EmpDAOImpl().getEmpById(rs.getInt("EMPID")));
				rewAndPun.setRewAndPunType(new RewAndPunTypeDAOImpl().getById(rs.getInt("REWANDPUNTYPE")));
				rewAndPun.setTime(rs.getDate("TIME"));
				rewAndPun.setContent(rs.getString("CONTENT"));				
				rewAndPun.setMoney(rs.getDouble("MONEY"));

				list.add(rewAndPun);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return page;
	}

	public Page<RewAndPun> getByTime(int pageNo, int pageSize, Date starttime,
			Date endtime) {
		Page<RewAndPun> page = new Page<RewAndPun>(pageNo, pageSize);
		int totalCount = 0;
		List<RewAndPun> list = new ArrayList<RewAndPun>();
		Emp emp = new Emp();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from RewAndPun";
			stat=conn.prepareStatement(sql);
			rs=stat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from RewAndPun where starttime >to_date(?,'hh-mm-dd') and starttime<to_date(?,'hh-mm-dd')) s where rownum <=?) where rn >=?";
			stat = conn.prepareStatement(sql);
			stat.setString(1, sdf.format(starttime));
			stat.setString(2, sdf.format(endtime));
			stat.setInt(3, page.getPageNo()*pageSize);
			stat.setInt(4,(page.getPageNo()-1)*pageSize+1);
			rs = stat.executeQuery();
			while (rs.next()) {
				RewAndPun rewAndPun =new RewAndPun();
				rewAndPun.setId(rs.getInt("ID"));
				rewAndPun.setTitle(rs.getString("TITLE"));
				rewAndPun.setEmp(new EmpDAOImpl().getEmpById(rs.getInt("EMPID")));
				rewAndPun.setRewAndPunType(new RewAndPunTypeDAOImpl().getById(rs.getInt("REWANDPUNTYPE")));
				rewAndPun.setTime(rs.getDate("TIME"));
				rewAndPun.setContent(rs.getString("CONTENT"));				
				rewAndPun.setMoney(rs.getDouble("MONEY"));

				list.add(rewAndPun);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return page;
	}

	public boolean update(RewAndPun rewAndPun) {
		try {
			conn = dbu.getConnection();
			String sql="update rewAndPun set title=?,empid=?,REWANDPUNTYPE=?,time=to_date(?,'hh-mm-dd'),CONTENT=?,money=?) where id=?";
			stat=conn.prepareStatement(sql);
			stat.setString(1, rewAndPun.getTitle());
			stat.setInt(2, rewAndPun.getEmp().getId());
			stat.setInt(3, rewAndPun.getRewAndPunType().getId());
			stat.setString(4, sdf.format(rewAndPun.getTime()));
			stat.setString(5, rewAndPun.getContent());
			stat.setDouble(9, rewAndPun.getMoney());
			
			if(stat.executeUpdate()>0){
				result=true;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return result;
	}
	public RewAndPun getById(int id)
	{
		RewAndPun rewAndPun =new RewAndPun();
		PreparedStatement pstat=null;
		try {
			conn = dbu.getConnection();
			String sql="select * from RewAndPun where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, id);
			rs=pstat.executeQuery();
			while(rs.next())
			{
				
				rewAndPun.setId(rs.getInt("ID"));
				rewAndPun.setTitle(rs.getString("TITLE"));
				rewAndPun.setEmp(new EmpDAOImpl().getEmpById(rs.getInt("EMPID")));
				rewAndPun.setRewAndPunType(new RewAndPunTypeDAOImpl().getById(rs.getInt("REWANDPUNTYPE")));
				rewAndPun.setTime(rs.getDate("TIME"));
				rewAndPun.setContent(rs.getString("CONTENT"));				
				rewAndPun.setMoney(rs.getDouble("MONEY"));
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}finally
		{
			dbu.closeConnection(conn, pstat, rs);
		}
		return rewAndPun;
	}
	public boolean updateRewAnd(RewAndPun rewAndPun)
	{
		try {
			conn = dbu.getConnection();
			String sql="update rewAndPun set title=?,REWANDPUNTYPE=?,CONTENT=?,money=? where id=?";
			stat=conn.prepareStatement(sql);
			stat.setString(1, rewAndPun.getTitle());
			stat.setInt(2, rewAndPun.getRewAndPunType().getId());
			
			stat.setString(3, rewAndPun.getContent());
			stat.setDouble(4, rewAndPun.getMoney());
			stat.setInt(5, rewAndPun.getId());
			if(stat.executeUpdate()>0){
				result=true;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return result;
	}
	
	
	
	public Page<RewAndPun> getAll(int pageNo, int pageSize ,String name) {
		Page<RewAndPun> page = new Page<RewAndPun>(pageNo, pageSize);
		int totalCount = 0;
		List<RewAndPun> list = new ArrayList<RewAndPun>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from RewAndPun r,emp e where e.id=r.empid and e.name like'%"+name+"%'";
			stat=conn.prepareStatement(sql);
			rs=stat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select r.* from RewAndPun r,emp e where  e.id=r.empid and  e.name like'%"+name+"%') s where rownum <=?) where rn >=?";
			stat = conn.prepareStatement(sql);
			
			stat.setInt(1, page.getPageNo()*pageSize);
			stat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = stat.executeQuery();
			while (rs.next()) {
				RewAndPun rewAndPun =new RewAndPun();
				rewAndPun.setId(rs.getInt("ID"));
				rewAndPun.setTitle(rs.getString("TITLE"));
				rewAndPun.setEmp(new EmpDAOImpl().getEmpById(rs.getInt("EMPID")));
				rewAndPun.setRewAndPunType(new RewAndPunTypeDAOImpl().getById(rs.getInt("REWANDPUNTYPE")));
				rewAndPun.setTime(rs.getDate("TIME"));
				rewAndPun.setContent(rs.getString("CONTENT"));				
				rewAndPun.setMoney(rs.getDouble("MONEY"));

				list.add(rewAndPun);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return page;
	}
}
